from t1 in <table1>
join t2 in <table2>
on new { Id = t1.Id, ConditionField = true }
equals new { Id = t2.ForeignId, ConditionField = t2.ConditionField }
into j
from r in j.DefaultIfEmpty()
where r == null
select t1.Id | SELECT t1.id
FROM
table1 AS t1
LEFT JOIN table2 AS t2
ON (t1.id = t2.foreign_id)
AND t2.condition_field
WHERE
(t2.id IS NULL) | Нет подзапросов и коррелированных запросов. Получен наиболее эффективный sql запрос. |
from t1 in <table1>
from t2 in <table2>
.Where(e => t1.Id == e.ForeignId && e.ConditionField == true)
.DefaultIfEmpty()
where t2 == null
select t1.Id | SELECT t1.id
FROM
table1 AS t1
LEFT JOIN (
SELECT
t2.id,
t2.foreign_id
FROM
table2 AS t2
WHERE
t2.condition_field
) AS t ON t1.id = t.foreign_id
WHERE
(t.id IS NULL) | Пока нет проверки t2.ConditionField, запрос корректный, но при добавлении проверки мы получаем подзапрос. |
<table1>
.Where(
e => !<table2>
.Where(e => e.ConditionField)
.Select(e => e.ForeignId)
.Contains(e.Id)
) | SELECT t1.id
FROM table1 AS t1
WHERE
NOT (
EXISTS (
SELECT
1
FROM
table2 AS t2
WHERE
t2.condition_field
AND (t2.foreign_id = t1.id)
)
) | Получаем коррелированный запрос. |